from typing import Optional

from fastapi import APIRouter, Depends, Query
from pydantic import BaseModel, Field
from sqlalchemy import text
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db

data_phone2_router = APIRouter(prefix="/phone", tags=["phone2"])


class OperateAlarmQueryParams(PaginationBase):
    """危化异常操作查询参数"""

    # 筛选条件
    fsiteNo: str = Field("", description="站点编号")
    fpointNo: str = Field("", description="点位编号")
    fbegdate: str = Field("", description="开始日期(yyyyMMdd)")
    fenddate: str = Field("", description="结束日期(yyyyMMdd)")
    boxFequipmentbarcode: str = Field("", description="箱子设备条码")
    cabFequipmentbarcode: str = Field("", description="柜子设备条码")
    ftakedateStart: str = Field("", description="开始时间")
    ftakedateEnd: str = Field("", description="结束时间")


class OperateAlarmDataItem(BaseModel):
    """危化异常操作数据项"""
    fupddate: str = Field(..., description="操作时间(yyyy-MM-ddTHH:mm:ss)")
    username: str = Field("", description="操作人姓名")
    fsite_no: str = Field(..., description="站点编号")
    fsite_name: str = Field(..., description="站点名称")
    fpoint_name: str = Field(..., description="点位名称")
    fconname: str = Field(..., description="控制器名称")
    fcabname: str = Field(..., description="柜子名称")
    fboxname: str = Field(..., description="箱子名称")
    faction: str = Field(..., description="操作类型")
    fbiztype: str = Field(..., description="业务类型")
    frfidbarcode: str = Field(..., description="RFID条码")
    fgoodname: str = Field(..., description="物品名称")
    fmessage: str = Field("", description="操作消息")


@data_phone2_router.get("/getOperateAlarm2", summary="危化：异常操作")
def getOperateAlarm2(query: OperateAlarmQueryParams = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select  a.fupddate,b.username,t.fsite_no,t.fsite_name,s.fpoint_name,g.fequipmenttag fconname"
    sql = sql + ",COALESCE(c.fequipmenttag, fcabbarcode) fcabname"
    sql = sql + ",COALESCE(d.fequipmenttag, fboxbarcode) fboxname"
    sql = sql + ",case faction when 0 then '取出' when 3 then '归还' when 1 then '入库' when 7 then '出库' when 8 then '盘点' end faction"
    sql = sql + ",case fbiztype when 2 then '取' else '还' end fbiztype, frfidbarcode"
    sql = sql + ",COALESCE(e.fgoodname, a.fgoodno) fgoodname,fmessage"
    sql = sql + " from i_warehouse_dtl a"
    sql = sql + " left join am_sys_user b on cast(b.id as varchar) = a.foperator"
    sql = sql + " left join lims.i_ts_cabinet c on c.fequipmentbarcode = a.fcabbarcode"
    sql = sql + " left join lims.i_ts_warehouse d on d.fequipmentbarcode = a.fboxbarcode"
    sql = sql + " left join i_ts_good_info e on e.fgoodno = a.fgoodno"
    sql = sql + " left join i_ts_controlbox f on f.fcatbarcode=c.fequipmentbarcode"
    sql = sql + " left join i_ts_box g on g.fequipmentbarcode=f.fequipmentbarcode"
    sql = sql + " left join a_ts_point s on s.fpoint_no=c.fpoint_no"
    sql = sql + " left join a_ts_site t on s.fsite_no = t.fsite_no"
    sql = sql + " where 1 = 1 and fwarringflag=1"
    if query.fsiteNo != "":
        sql = sql + " and a.fsite_no= :fsiteNo "
        param["fsiteNo"] = query.fsiteNo
    if query.fpointNo != "":
        sql = sql + " and s.fpointNo= :fpointNo "
        param["fpointNo"] = query.fpointNo
    if query.ftakedateStart != "":
        sql = sql + " and to_char(a.fupddate,'yyyymmdd') between  :ftakedateStart  and  :ftakedateEnd "
        param["ftakedateStart"] = query.ftakedateStart
        param["ftakedateEnd"] = query.ftakedateEnd
    if query.boxFequipmentbarcode != "":
        sql = sql + " and a.fboxbarcode= :boxFequipmentbarcode "
        param["boxFequipmentbarcode"] = query.boxFequipmentbarcode
    if query.cabFequipmentbarcode != "":
        sql = sql + " and a.fcabbarcode= :cabFequipmentbarcode "
        param["cabFequipmentbarcode"] = query.cabFequipmentbarcode

    sql = sql + " UNION"
    sql = sql + " select h.fcheckdate fupddate,'' username,u.fsite_no,u.fsite_name,n.fpoint_name,g.fequipmenttag fconname,i.fequipmenttag fcabname, j.fequipmenttag fboxname"
    sql = sql + " ,t.fdictname faction,t.fdictmemo fbiztype,k.frfidbarcode,l.fgoodname,h.fmessage"
    sql = sql + " from i_day_report h"
    sql = sql + " left join lims.i_ts_cabinet i on i.fequipmentbarcode = h.fcabbarcode"
    sql = sql + " left join lims.i_ts_warehouse j on j.fequipmentbarcode = h.fboxbarcode"
    sql = sql + " left join i_warehouse_inv k on k.frfidbarcode = h.frfidbarcode and k.fstate=3"
    sql = sql + " left join i_ts_good_info l on k.fgoodno = l.fgoodno"
    sql = sql + " left join i_ts_controlbox m on m.fcatbarcode=i.fequipmentbarcode"
    sql = sql + " left join i_ts_box g on g.fequipmentbarcode=m.fequipmentbarcode"
    sql = sql + " left join a_ts_point n on i.fpoint_no = n.fpoint_no"
    sql = sql + " left join a_ts_site u on u.fsite_no = n.fsite_no"
    sql = sql + " left join s_ts_dict t on h.ftype=cast(t.fdictno as int) and t.fdicttype='3009'"
    sql = sql + " where 1=1"
    sql = sql + " and h.ftype in ('101','104','105','107','108','109','110','111')"
    if query.fsiteNo != "":
        sql = sql + " and h.fsite_no= :fsiteNo "
    if query.fpointNo != "":
        sql = sql + " and n.fpointNo= :fpointNo "
    if query.ftakedateStart != "":
        sql = sql + " and to_char(h.fcheckdate,'yyyymmdd') between  :ftakedateStart  and  :ftakedateEnd "
    if query.boxFequipmentbarcode != "":
        sql = sql + " and h.fboxbarcode= :boxFequipmentbarcode "
    if query.cabFequipmentbarcode != "":
        sql = sql + " and h.fcabbarcode= :cabFequipmentbarcode "
    sql = sql + " order by 1"
    data_2000 = db.execute(text(sql), param).fetchall()

    # 计算总笔数+总页码
    counts = len(data_2000)
    page_num, a = divmod(int(counts), int(query.offset))
    if a > 0:
        page_num += 1
    info_dict = {}
    info_dict['total'] = counts
    info_dict['pages'] = query.offset
    info_dict['limit'] = query.offset
    info_dict['page'] = query.pageIndex

    # 取出指定页码数据
    start = (int(query.pageIndex) - 1) * int(query.offset)
    end = int(query.pageIndex) * int(query.offset)
    data = data_2000[start:end]

    data_list = []
    for key in range(len(data)):
        d1 = {}
        current_datetime = data[key][0].strftime('%Y-%m-%dT%H:%M:%S')
        d1['fupddate'] = current_datetime
        d1['username'] = data[key][1]
        d1['fsite_no'] = data[key][2]
        d1['fsite_name'] = data[key][3]
        d1['fpoint_name'] = data[key][4]
        d1['fconname'] = data[key][5]
        d1['fcabname'] = data[key][6]
        d1['fboxname'] = data[key][7]
        d1['faction'] = data[key][8]
        d1['fbiztype'] = data[key][9]
        d1['frfidbarcode'] = data[key][10]
        d1['fgoodname'] = data[key][11]
        d1['fmessage'] = data[key][12]
        data_list.append(d1)

    info_dict = {"list": data_list, "limit": int(query.offset), "page": int(query.pageIndex), "pages": int(page_num),
                 "total": int(counts)}
    return BaseResponse.success(data=info_dict)


@data_phone2_router.get("/getExceUseAlarm2", summary="危化：异常登录")
def getExceUseAlarm2(query: OperateAlarmQueryParams = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select a.flogindate,e.fsite_name,d.fpoint_name,c.fequipmenttag,b.username,a.fnote,d.fsite_no"
    sql = sql + " from i_tc_usewarring a,am_sys_user b, i_ts_box c,a_ts_point d, a_ts_site e"
    sql = sql + " where 1 = 1"
    sql = sql + " and a.user_id = b.id"
    sql = sql + " and c.fequipmentbarcode = a.fequipmentbarcode"
    sql = sql + " and c.fpoint_no = d.fpoint_no"
    sql = sql + " and d.fsite_no = e.fsite_no"
    if query.fsiteNo != "":
        sql = sql + " and a.fsite_no= :fsiteNo "
        param['fsiteNo'] = query.fsiteNo
    if query.fpointNo != "":
        sql = sql + " and c.fpointNo= :fpointNo "
        param['fpointNo'] = query.fpointNo
    if query.ftakedateStart != "":
        sql = sql + " and to_char(a.flogindate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd "
        param['ftakedateStart'] = query.ftakedateStart
        param['ftakedateEnd'] = query.ftakedateEnd
    sql = sql + " order by a.flogindate"
    data_2000 = db.execute(text(sql), param).fetchall()

    # 计算总笔数+总页码
    counts = len(data_2000)
    page_num, a = divmod(int(counts), int(query.offset))
    if a > 0:
        page_num += 1
    info_dict = {}
    info_dict['total'] = counts
    info_dict['pages'] = query.offset
    info_dict['limit'] = query.offset
    info_dict['page'] = query.pageIndex

    # 取出指定页码数据
    start = (int(query.pageIndex) - 1) * int(query.offset)
    end = int(query.pageIndex) * int(query.offset)
    data = data_2000[start:end]

    data_list = []
    for key in range(len(data)):
        d1 = {}
        current_datetime = data[key][0].strftime('%Y-%m-%dT%H:%M:%S')
        d1['flogindate'] = current_datetime
        d1['fsite_name'] = data[key][1]
        d1['fpoint_name'] = data[key][2]
        d1['fequipmenttag'] = data[key][3]
        d1['username'] = data[key][4]
        d1['fnote'] = data[key][5]
        d1['fsite_no'] = data[key][6]
        data_list.append(d1)

    info_dict = {"list": data_list, "limit": int(query.offset), "page": int(query.pageIndex), "pages": int(page_num),
                 "total": int(counts)}
    return BaseResponse.success(data=info_dict)
